/*
* Conditions Of Use
*
* This software was developed by employees of the Sigmatrix(Beijing) Corporation.
* This software is provided by sigmatrix as a service and is expressly
* provided "AS IS."  Sigmatrix MAKES NO WARRANTY OF ANY KIND, EXPRESS, IMPLIED
* OR STATUTORY, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTY OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, NON-INFRINGEMENT
* AND DATA ACCURACY.  Sigmatrix does not warrant or make any representations
* regarding the use of the software or the results thereof, including but
* not limited to the correctness, accuracy, reliability or usefulness of
* the software.
*
* Permission to use this software is contingent upon your acceptance
* of the terms of this agreement.
*
*/
package com.sigmatrix.h5.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.sigmatrix.h5.dao.WarehouseStockDetailsDao;
import com.sigmatrix.h5.dao.impl.common.impl.BaseDaoImpl;
import com.sigmatrix.h5.entity.WarehouseStock;
import com.sigmatrix.h5.entity.WarehouseStockDetails;
import com.sigmatrix.h5.enums.TableEnum;
import com.sigmatrix.h5.exception.DaoException;
import com.sigmatrix.h5.utils.Cons;

/**
 * @ClassName: WarehouseStockDetailsDaoImpl
 * @Description: 库存详情数据库操作接口实现
 * @author youjun 
 * @date 2017-06-14 10:18:54
 */
@Repository(value="warehouseStockDetailsDao")
public class WarehouseStockDetailsDaoImpl extends BaseDaoImpl<WarehouseStock> implements WarehouseStockDetailsDao {

	private static final Logger LOGGER = LoggerFactory.getLogger(WarehouseStockDetailsDaoImpl.class);
	@Autowired
	private transient JdbcTemplate jdbcTemplate;

	/**
	 * (non-Javadoc)
	 *@Title: findById
	 *@Description: 
	 *@see com.sigmatrix.sc.production.mgt.dao.WarehouseStockDetailsDao#findById(java.lang.Integer)
	 *@author youjun
	 *@date 2017年6月14日 上午10:41:37
	 */
	@Override
	public WarehouseStockDetails findById(final Integer id, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("SELECT * FROM ").append(getTname(null, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" WHERE _id = ?");
		final Object[] params = new Object[] { id };
		final List<WarehouseStockDetails> list = jdbcTemplate.query(sql.toString(), params, new WarehouseStockDetailsRowMapper());
		if (CollectionUtils.isEmpty(list)) {
			return null;
		}
		return list.get(0);
	}

	/**
	 * (non-Javadoc)
	 *@Title: save
	 *@Description: 
	 *@see com.sigmatrix.sc.production.mgt.dao.WarehouseStockDetailsDao
	 *	#save(com.sigmatrix.sc.production.mgt.entity.WarehouseStockDetails)
	 *@author youjun
	 *@date 2017年6月14日 上午10:41:41
	 */
	@Override
	public int save(final WarehouseStockDetails warehouseStockDetails, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder(
				"INSERT INTO  ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" (_stock_id, _code, _status, _creator_id, _create_time, _remarks) VALUES (?, ?, ?, ?, ?, ?)");
		final Object[] params = new Object[] { warehouseStockDetails.getStockId(), warehouseStockDetails.getCode(),
				warehouseStockDetails.getStatus(), warehouseStockDetails.getCreatorId(),
				warehouseStockDetails.getCreateTime(), warehouseStockDetails.getRemarks() };
		final int[] argTypes = new int[] { Types.INTEGER, Types.VARCHAR, Types.SMALLINT, Types.INTEGER, Types.TIMESTAMP,
				Types.VARCHAR };
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}

	/**(non-Javadoc)
	 * @throws DaoException 
	 *@Title: batchSave
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.WarehouseStockDetailsDao#batchSave(java.util.List, java.lang.String)
	 *@author youjun
	 *@date 2017年8月27日 下午9:14:50
	 */
	@Override
	public int batchSave(final List<WarehouseStockDetails> warehouseStockDetailsList, final String enterpriseCode) throws DaoException {

		if(StringUtils.isBlank(enterpriseCode)
				||CollectionUtils.isEmpty(warehouseStockDetailsList)) {
			throw new DaoException("批量添加库存详情参数有误");
		}
		final StringBuilder sql = new StringBuilder()
				.append("INSERT INTO ")
				.append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK_DETAILS))
				.append("(_stock_id, _code, _status, _creator_id, _create_time, _remarks) VALUES (?, ?, ?, ?, ?, ?)");
		final List<Object[]> paramList = new ArrayList<Object[]>();
		for(final WarehouseStockDetails warehouseStockDetails:warehouseStockDetailsList) {
			final Object[] params = new Object[] { 
					warehouseStockDetails.getStockId(), warehouseStockDetails.getCode(),
					warehouseStockDetails.getStatus(), warehouseStockDetails.getCreatorId(),
					warehouseStockDetails.getCreateTime(), warehouseStockDetails.getRemarks() 
					};
			paramList.add(params);
		}
		final int[] results = jdbcTemplate.batchUpdate(sql.toString(), paramList);
		return results[0];
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: update
	 *@Description: 
	 *@see com.sigmatrix.sc.production.mgt.dao.WarehouseStockDetailsDao#update(com.sigmatrix.sc.production.mgt.entity.WarehouseStockDetails)
	 *@author youjun
	 *@date 2017年6月14日 上午10:41:45
	 */
	@Override
	public int update(final WarehouseStockDetails warehouseStockDetails, final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder(
				"UPDATE  ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append("  SET _stock_id = ?, _code = ?, _status = ?, _creator_id = ?, _create_time = ?, _remarks = ? WHERE _id = ?");
		final Object[] params = new Object[] { warehouseStockDetails.getStockId(), warehouseStockDetails.getCode(),
				warehouseStockDetails.getStatus(), warehouseStockDetails.getCreatorId(),
				warehouseStockDetails.getCreateTime(), warehouseStockDetails.getRemarks(),
				warehouseStockDetails.getId() };
		final int[] argTypes = new int[] { Types.INTEGER, Types.VARCHAR, Types.SMALLINT, Types.INTEGER, Types.TIMESTAMP,
				Types.VARCHAR, Types.INTEGER };
		return jdbcTemplate.update(sql.toString(), params, argTypes);
	}

	/**(non-Javadoc)
	 * @throws DaoException 
	 *@Title: batchUpdateStatus
	 *@Description: 
	 *@see com.sigmatrix.sc.handheld.inter.dao.WarehouseStockDetailsDao#batchUpdateStatus(int, java.util.List, short, java.lang.String)
	 *@author youjun
	 *@date 2017年8月28日 下午5:11:42
	 */
	@Override
	public int batchUpdateStatus(final Long stockId, final List<String> batchBoxList, final short updateStatus, final String enterpriseCode) throws DaoException {

		try {
			StringBuilder sql = new StringBuilder("UPDATE ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK_DETAILS));
			sql.append(" SET _status = ? WHERE _stock_id = ? AND _status = ?");
			final List<Object> paramList = new ArrayList<Object>();
			final List<Integer> argTypeList = new ArrayList<Integer>();
			paramList.add(updateStatus);
			argTypeList.add(Types.SMALLINT);
			paramList.add(stockId);
			argTypeList.add(Types.INTEGER);
			paramList.add(Cons.WH_STOCK_DETAILS_STATUS_IN_STORAGE);
			argTypeList.add(Types.SMALLINT);
			if(!CollectionUtils.isEmpty(batchBoxList)) {
				sql.append(" AND _code IN (");
				for(final String box:batchBoxList) {
					sql.append("?,");
					paramList.add(box);
					argTypeList.add(Types.VARCHAR);
				}
				sql = new StringBuilder(sql.substring(0, sql.length()-1));
				sql.append(")");
			}
			final int[] argTypes = super.getIntArray(argTypeList);
			return jdbcTemplate.update(sql.toString(), paramList.toArray(), argTypes);
		} catch(Exception e) {
			throw new DaoException(e);
		}
	}
	
	/**
	 * (non-Javadoc)
	 *@Title: updateStatus
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.WarehouseStockDetailsDao#updateStatus(java.lang.Long, java.lang.String, short, java.lang.String)
	 *@author gaomingkai
	 *@date 2018年1月26日 下午1:08:28
	 */
	@Override
	public int updateStatus(Long stockId, String code, short updateStatus, String enterpriseCode) throws DaoException {

		try {
			StringBuilder sql = new StringBuilder("UPDATE ").append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK_DETAILS));
			sql.append(" SET _status = ? WHERE _stock_id = ? AND _status = ? AND _code = ?");
			final Object[] params = new Object[]{updateStatus, stockId, Cons.WH_STOCK_DETAILS_STATUS_IN_STORAGE, code};
			final int[] argTypes = new int[]{Types.SMALLINT, Types.BIGINT, Types.SMALLINT, Types.VARCHAR};
			return jdbcTemplate.update(sql.toString(), params, argTypes);
		} catch(Exception e) {
			throw new DaoException(e);
		}
	}

	
	/**
	 * (non-Javadoc)
	 *@Title: findListByCondition
	 *@Description: 
	 *@see com.sigmatrix.sc.production.mgt.dao.WarehouseStockDetailsDao#findListByCondition(com.sigmatrix.sc.production.mgt.entity.WarehouseStockDetails)
	 *@author youjun
	 *@date 2017年6月14日 上午10:42:01
	 */
	@Override
	public List<WarehouseStockDetails> findListByCondition(final WarehouseStockDetails warehouseStockDetails, 
			final String enterpriseCode) throws DaoException {
		final StringBuilder sql = new StringBuilder("SELECT * FROM  ")
				.append(getTname(enterpriseCode, TableEnum.WAREHOUSE_STOCK_DETAILS));
		final List<Object> paramList = new ArrayList<Object>();
		sql.append(" WHERE _status <> ?");
		paramList.add(Cons.STATUS_DELETE);
		try {
			return jdbcTemplate.query(sql.toString(), paramList.toArray(), new WarehouseStockDetailsRowMapper());
		} catch (DataAccessException e) {
			throw new DaoException(e);
		}
	}
	
	
	/**
	 * (non-Javadoc)
	 *@Title: countByStockId
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.WarehouseStockDetailsDao#countByStockId(java.lang.String, java.lang.Long)
	 *@author 
	 *@date 2017年9月28日 下午7:39:32
	 */
	@Override
	public Integer countByStockId(final String eseCode,final Long stockId, final Short status){
		final StringBuilder sql = new StringBuilder()
				.append("SELECT count(*) FROM  ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK_DETAILS))
				.append(" WHERE _stock_id = ").append(stockId).append(" AND _status = ").append(status);
		return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
	}
	
	
	/**
	 * (non-Javadoc)
	 *@Title: findByCondition
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.WarehouseStockDetailsDao#findByCondition(java.lang.String, java.lang.Long, java.lang.Integer, java.lang.String)
	 *@date 2017年10月12日 下午7:09:08
	 */
	public Integer countByCondition(final String eseCode,
			final Integer warehosueId,final Integer productId,final String code,final Short status,final boolean isflag){
		final StringBuilder sql = new StringBuilder();
		if (isflag) {
			sql.append("SELECT COUNT(*) FROM  ")
			.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" a ")
			.append(" WHERE a._stock_id in ( ")
				.append(" SELECT b._id FROM ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK)).append(" b ")
				.append(" WHERE b._warehouse_id = ").append(warehosueId)
				.append("  AND b._product_id = ").append(productId)
			.append(" )")
			.append(" AND  a._code = '").append(code).append("'");
		}
		else {
			sql.append("SELECT COUNT(*) FROM  ")
			.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" a ")
			.append(" WHERE a._stock_id in ( ")
				.append(" SELECT b._id FROM ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK)).append(" b ")
				.append(" WHERE b._warehouse_id = ").append(warehosueId)
				.append("  AND b._product_id = ").append(productId)
			.append(" )")
			.append(" AND  a._code = '").append(code).append("'").append(" AND  a._status = ").append(status);
		}
				
		LOGGER.info(sql.toString());
		return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
	}

	
	@Override
	public List<WarehouseStockDetails> findByCondition(String eseCode, Integer warehosueId, Integer productId,
			String code, Short status) {
		final StringBuilder sql = new StringBuilder();
		//sql.append("SELECT COUNT(*) FROM  ")
		sql.append("SELECT * FROM  ")
		.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" a ")
		.append(" WHERE a._stock_id in ( ")
			.append(" SELECT b._id FROM ")
			.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK)).append(" b ")
			.append(" WHERE b._warehouse_id = ").append(warehosueId)
			.append("  AND b._product_id = ").append(productId)
		.append(" )")
		.append(" AND  a._code = '").append(code).append("'");
		LOGGER.info(sql.toString());
		return jdbcTemplate.query(sql.toString(), new WarehouseStockDetailsRowMapper());
	}
	/**
	 * 
	 *@Title: countByConditionOut
	 *@Description: TODO()
	 *@return Integer
	 *@author gaomingkai
	 *@date 2017年10月31日 下午11:02:06
	 *@param eseCode
	 *@param warehosueId
	 *@param productId
	 *@param code
	 *@param status
	 *@return
	 */
	public Integer countByConditionOut(final String eseCode,
			final Integer warehosueId,final Integer productId,final String code, final int status,final boolean isflag){
		final StringBuilder sql = new StringBuilder();
		if (isflag) {
			sql.append("SELECT COUNT(*) FROM  ")
			.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" a ")
			.append(" WHERE a._stock_id in ( ")
				.append(" SELECT b._id FROM ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK)).append(" b ")
				.append(" WHERE b._warehouse_id = ").append(warehosueId)
				.append("  AND b._product_id = ").append(productId)
			.append(" )")
			.append(" AND  a._code = '").append(code).append("'").append(" AND  a._status = ").append(status);
		}
			
		else {
			sql.append("SELECT COUNT(*) FROM  ")
			.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" a ")
			.append(" WHERE a._stock_id in ( ")
				.append(" SELECT b._id FROM ")
				.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK)).append(" b ")
				.append(" WHERE b._warehouse_id = ").append(warehosueId)
				.append("  AND b._product_id = ").append(productId)
			.append(" )")
			.append(" AND  a._code = '").append(code).append("'");
		}	
		LOGGER.info(sql.toString());
		return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
	}


	@Override
	public List<WarehouseStockDetails> findByConditionOut(String eseCode, Integer warehosueId, Integer productId,
			String code, int stauts) {
		final StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) FROM  ")
		.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" a ")
		.append(" WHERE a._stock_id in ( ")
			.append(" SELECT b._id FROM ")
			.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK)).append(" b ")
			.append(" WHERE b._warehouse_id = ").append(warehosueId)
			.append("  AND b._product_id = ").append(productId)
		.append(" )")
		.append(" AND  a._code = '").append(code).append("'");
		return jdbcTemplate.query(sql.toString(), new WarehouseStockDetailsRowMapper());
	}

	/**
	 * @ClassName: WarehouseStockDetailsRowMapper
	 * @Description: 封装返回的实体
	 * @author youjun @date 2017-06-14 10:18:54
	 */
	public class WarehouseStockDetailsRowMapper implements RowMapper<WarehouseStockDetails> {
		@Override
		public WarehouseStockDetails mapRow(final ResultSet resultSet, final int rowNum) throws SQLException {
			final WarehouseStockDetails warehouseStockDetails = new WarehouseStockDetails();
			warehouseStockDetails.setId(resultSet.getLong("_id"));
			warehouseStockDetails.setStockId(resultSet.getLong("_stock_id"));
			warehouseStockDetails.setCode(resultSet.getString("_code"));
			warehouseStockDetails.setStatus(resultSet.getShort("_status"));
			warehouseStockDetails.setCreatorId(resultSet.getInt("_creator_id"));
			warehouseStockDetails.setCreateTime(resultSet.getTimestamp("_create_time"));
			warehouseStockDetails.setRemarks(resultSet.getString("_remarks"));
			return warehouseStockDetails;
		}
	}

	/**
	 * (non-Javadoc)
	 *@Title: countByCode
	 *@Description: 
	 *@see com.sigmatrix.h5.dao.WarehouseStockDetailsDao#countByCode(java.lang.String, java.lang.String)
	 *@author gaomingkai
	 *@date 2017年12月27日 上午11:16:00
	 */
	@Override
	public Integer countByCode(String eseCode, String code) {
		final StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(*) FROM  ")
		.append(getTname(eseCode, TableEnum.WAREHOUSE_STOCK_DETAILS)).append(" a ")
		.append(" WHERE  a._code = '").append(code).append("'");	
	LOGGER.info("验证箱码是否存在查询sql:"+sql.toString());
	return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
	}




}
